﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySql.Web;


namespace Artemis
{
    public partial class Agents : Form
    {
        public Agents(Boolean Switch,string typeID)
        {
            if (Switch == true)
            {
                connectionstring = ConfigurationManager.ConnectionStrings["BackupDataBase"].ConnectionString.ToString();
            }
            else
            {
                connectionstring = ConfigurationManager.ConnectionStrings["MainDataBase"].ConnectionString.ToString();
            }
            agenttypeID = typeID;
            InitializeComponent();
        }
        public string agenttypeID = null;
        public string divisionName = null;
        public string connectionstring = null;
        public string level = "1";
        public bool boot = true;
        public bool factionexe = false;
        public bool corpexe = false;
        public bool resexe = false;
        public bool lvlexe = false;
        public bool divexe = false;
        public static DataSet getcorporationsn(string agentTypeId,string factionName,string connectionstring,string level)
        {
            DataSet names = new DataSet();
            MySqlConnection conn = null;
            conn = new MySqlConnection();
            conn.ConnectionString = connectionstring;
            string sql = "SELECT CorporationNames FROM corporationnameszh where corporationID in(select corporationID from agtagents where agentTypeID = @agentTypeID and level = @level And corporationID in ( select corporationID from crpnpccorporations where factionID =(select factionID from chrfactions where factionName = @factionName))) ORDER BY CorporationNames DESC ;";
            conn.Open();
            MySqlCommand commd = new MySqlCommand("set names utf8", conn);
            commd.ExecuteNonQuery();
            commd.Dispose();
            MySqlCommand query = new MySqlCommand(sql, conn);
            query.Parameters.Add(new MySqlParameter("@agentTypeID", agentTypeId));
            query.Parameters.Add(new MySqlParameter("@factionName", factionName));
            query.Parameters.Add(new MySqlParameter("@level", level));
            MySqlDataAdapter mda = new MySqlDataAdapter(query);
            mda.Fill(names, "Corporations");
            conn.Close();
            return names;
        }    //获取军团信息
        public static DataSet getstations(string corp, string level,string agentTypeId,string division,string connectionstring)
        {
            DataSet stations = new DataSet();
            MySqlConnection conn = null;
            conn = new MySqlConnection();
            conn.ConnectionString = connectionstring;
            string sql = "SELECT stationName FROM stastations WHERE stationID IN (SELECT locationID FROM agtagents WHERE corporationID IN ( SELECT corporationID FROM `corporationnameszh` WHERE CorporationNames=@corp) AND level = @level AND agentTypeID=@agentTypeID AND divisionID IN( SELECT divisionID FROM `crpnpcdivisions` WHERE divisionName = @division)) ORDER BY stationName DESC ;";
            conn.Open();
            MySqlCommand commd = new MySqlCommand("set names utf8", conn);
            commd.ExecuteNonQuery();
            commd.Dispose();
            MySqlCommand query = new MySqlCommand(sql, conn);
            query.Parameters.Add(new MySqlParameter("@corp",corp));
            query.Parameters.Add(new MySqlParameter("@agentTypeID", agentTypeId));
            query.Parameters.Add(new MySqlParameter("@level", level));
            query.Parameters.Add(new MySqlParameter("@division", division));
            MySqlDataAdapter mda = new MySqlDataAdapter(query);
            mda.Fill(stations, "stations");
            conn.Close();
            return stations;
        }     //获取空间站信息
        public static List<object> refresh(DataSet input, string TableName, string columnName)
        {
            List<object> stalist = new List<object>();
            stalist.Add("N/A");
            int i = 0;
            int r = 0;
            for (i = input.Tables[TableName].Rows.Count - 1; i >= 0; i--)
            {
                stalist.Add(input.Tables[TableName].Rows[r][columnName]);
                r++;
            }
            return stalist;
        }      //带有空行的comboBox的数据源刷新方法
        public static List<object> refreshnoemptyline(DataSet input, string TableName, string columnName)
        {
            List<object> stalist = new List<object>();
            int i = 0;
            int r = 0;
            for (i = input.Tables[TableName].Rows.Count - 1; i >= 0; i--)
            {
                stalist.Add(input.Tables[TableName].Rows[r][columnName]);
                r++;
            }
            return stalist;
        }      //无空行的comboBox数据源刷新方法
        public static DataSet getresearch(string corp, string level,string connectionstring)
        {
            DataSet stations = new DataSet();
            MySqlConnection conn = null;
            conn = new MySqlConnection();
            conn.ConnectionString = connectionstring;
            string sql = "SELECT DISTINCT typeName from invtypes where typeID IN (SELECT typeID from agtresearchagents where agentID in (select agentID from agtagents where corporationID in (select corporationID from `corporationnameszh` where CorporationNames = @corp)AND level = @level)) ORDER BY typeName DESC ;";
            conn.Open();
            MySqlCommand commd = new MySqlCommand("set names utf8", conn);
            commd.ExecuteNonQuery();
            commd.Dispose();
            MySqlCommand query = new MySqlCommand(sql, conn);
            query.Parameters.Add(new MySqlParameter("@corp", corp));
            query.Parameters.Add(new MySqlParameter("@level", level));
            MySqlDataAdapter mda = new MySqlDataAdapter(query);
            mda.Fill(stations, "research");
            conn.Close();
            return stations;
        }  //获取科研代理人研究信息
        public static DataSet getresSta(string corp, string level, string typeName,string connectionstring)
        {
            DataSet stations = new DataSet();
            MySqlConnection conn = null;
            conn = new MySqlConnection();
            conn.ConnectionString = connectionstring;
            Random ID = new Random();
            int id = ID.Next();
            string sql = "CREATE TEMPORARY TABLE tmp.tmp_loc"+id+" select locationID from agtagents where agentID in(select agentID from agtresearchagents where typeID in (select typeID from invtypes where typeName = @typeName))and level = @level and corporationID in(select corporationID from `corporationnameszh` where CorporationNames = @corp );";
            
            conn.Open();
            MySqlCommand commd = new MySqlCommand("set names utf8", conn);
            commd.ExecuteNonQuery();
            commd.Dispose();
            MySqlCommand query0 = new MySqlCommand(sql, conn);
            query0.Parameters.Add(new MySqlParameter("@corp", corp));
            query0.Parameters.Add(new MySqlParameter("@level", level));
            query0.Parameters.Add(new MySqlParameter("@typeName", typeName));
            query0.ExecuteNonQuery();
            string sql1 = "select stationName from stastations where stationID in (select locationID from tmp.tmp_loc"+id+");";
            MySqlCommand query = new MySqlCommand(sql1, conn);
            MySqlDataAdapter mda = new MySqlDataAdapter(query);
            mda.Fill(stations, "stations");
            conn.Close();
            return stations;
        }    //获取科研代理人所在空间站信息
        private static object CreateItem<T1>(DataRow row)
        {
            throw new NotImplementedException();
        }  

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
          

           
        }

        private void Agents_Load(object sender, EventArgs e)
        {
            try
            {
                if (agenttypeID == "2")
                {
                    cmbRestype.Enabled = false;
                }
                if (agenttypeID == "4")
                {
                    cmbRestype.Enabled = true;
                }
                DataSet FactionNames = new DataSet();
                string sql = null;
                sql = "select factionName from `chrfactions` where factionID in ( select distinct factionID from `crpnpccorporations` where corporationID in( select distinct corporationID from agtagents where agentTypeID = @agentTypeID));";
                MySqlCommand query = new MySqlCommand(sql);
                query.Parameters.Add(new MySqlParameter("@agentTypeID", agenttypeID));
                FactionNames = EveCentral.getdata(query, "factions", connectionstring);
                List<object> factionName = new List<object>();
                factionName = refresh(FactionNames, "factions", "factionName");
                cmbFaction.DataSource = factionName;
                
            }
            catch (MySqlException k)
            {
                MessageBox.Show("SQL ERROR" + k);
            }
            finally
            {
                boot = false;
            }
          }
        

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void cmbCorp_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (boot == false)
                {
                    if (factionexe == true)
                    {
                        corpexe = false;
                        DataSet stas = new DataSet();
                        stas = getstations(cmbCorp.Text, cmbLvl.Text, agenttypeID, divisionName, connectionstring);
                        cmbSta.DataSource = refresh(stas, "stations", "itemName");
                        if (agenttypeID == "4")
                        {
                            DataSet Research = new DataSet();
                            Research = getresearch(cmbCorp.Text, cmbLvl.Text, connectionstring);
                            cmbRestype.DataSource = refresh(Research, "research", "typeName");

                        }
                        string sql = null;
                        string corpname = cmbCorp.Text;
                        string level = cmbLvl.Text;
                        sql = "SELECT divisionName FROM crpnpcdivisions WHERE divisionID IN (SELECT DISTINCT divisionID FROM agtagents WHERE agentTypeID = @agentTypeID AND corporationID IN( SELECT corporationID FROM `corporationnameszh` WHERE CorporationNames = @corpName) AND level=@level) ORDER BY divisionName DESC ;";
                        DataSet divs = new DataSet();
                        MySqlCommand query = new MySqlCommand(sql);
                        query.Parameters.Add(new MySqlParameter("@agentTypeID", agenttypeID));
                        query.Parameters.Add(new MySqlParameter("@corpName", corpname));
                        query.Parameters.Add(new MySqlParameter("@level", level));
                        divs = EveCentral.getdata(query, "Divisions", connectionstring);
                        cmbDiv.DataSource = refresh(divs, "Divisions", "divisionName");
                    }
                }
            }
            catch (MySqlException k)
            {
                MessageBox.Show("SQL ERROR" + k);
            }
            finally
            {
                if (cmbCorp.Text != "N/A")
                {
                    corpexe = true;
                }
            }
        }

        private void cmbLvl_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                DataSet stas = new DataSet();
                stas = getstations(cmbCorp.Text, cmbLvl.Text, agenttypeID,divisionName,connectionstring);
                cmbSta.DataSource = refresh(stas, "stations", "itemName");
                if (agenttypeID == "4")
                {
                    DataSet Research = new DataSet();
                    Research = getresearch(cmbCorp.Text, cmbLvl.Text,connectionstring);
                    cmbRestype.DataSource = refresh(Research, "research", "typeName");

                }
                string sql = null;
                string corpname = cmbCorp.Text;
                level = cmbLvl.Text;
                sql = "SELECT divisionName FROM crpnpcdivisions WHERE divisionID IN (SELECT DISTINCT divisionID FROM agtagents WHERE agentTypeID = @agentTypeID AND corporationID IN( SELECT corporationID FROM `corporationnameszh` WHERE CorporationNames = @corpName) AND level=@level ) ORDER BY divisionName DESC ;";
                DataSet divs = new DataSet();
                MySqlCommand query = new MySqlCommand(sql);
                query.Parameters.Add(new MySqlParameter("@agentTypeID", agenttypeID));
                query.Parameters.Add(new MySqlParameter("@corpName", corpname));
                query.Parameters.Add(new MySqlParameter("@level", level));
                divs = EveCentral.getdata(query, "Divisions", connectionstring);
                cmbDiv.DataSource = refresh(divs, "Divisions", "divisionName");
            }
            catch (MySqlException k)
            {
                MessageBox.Show("SQL ERROR" + k);
            }
            finally
            {
                cmbFaction_SelectedIndexChanged(sender, e);
            }
        }

        private void cmbRestype_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (divexe == true)
                {
                    resexe = false;
                    DataSet ResSta = new DataSet();
                    ResSta = getresSta(cmbCorp.Text, cmbLvl.Text, cmbRestype.Text, connectionstring);
                    cmbSta.DataSource = refresh(ResSta, "stations", "stationName");
                }
            }
            catch (MySqlException k)
            {
                MessageBox.Show("SQL ERROR" + k);
            }
            finally
            {
                if (cmbRestype.Text != "N/A")
                {
                    resexe = true;
                }
            }
        }

        private void cmbFaction_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                factionexe = false;
                string facName = cmbFaction.Text;
                DataSet corpnames = new DataSet();
                corpnames = getcorporationsn(agenttypeID,facName,connectionstring,level);
                List<object> corpname = new List<object>();
                corpname = refresh(corpnames, "Corporations", "CorporationNames");
                cmbCorp.DataSource = corpname;
                if (agenttypeID == "4")
                {
                    DataSet rcorpnames = new DataSet();
                    rcorpnames = getcorporationsn(agenttypeID, facName,connectionstring,level);
                    cmbCorp.DataSource = refresh(rcorpnames, "Corporations", "CorporationNames");
                }

            }
            catch (MySqlException k)
            {
                MessageBox.Show("SQL ERROR" + k);
            }
            finally
            {
                if (cmbFaction.Text != "N/A")
                {
                    factionexe = true;
                }
            }

        }

        private void cmbDiv_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (corpexe == true)
                {
                    divexe = false;
                    string corp = cmbCorp.Text;
                    string lvl = cmbLvl.Text;
                    divisionName = cmbDiv.Text;
                    DataSet stas = new DataSet();
                    stas = getstations(corp, lvl, agenttypeID, divisionName, connectionstring);
                    cmbSta.DataSource = refresh(stas, "stations", "stationName");
                }
                
            }
            catch (MySqlException k)
            {
                MessageBox.Show("SQL ERROR" + k);
            }
            finally
            {
                if (cmbDiv.Text != "N/A")
                {
                    divexe = true;
                }
            }

        }
    }
}
